﻿using Ins.Office;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Data;
using System.Data.SqlClient;
using Ins.Office.Common;

namespace Ins.Office.Testing
{
    
    
    /// <summary>
    ///This is a test class for XLDataSetSheetWriterTest and is intended
    ///to contain all XLDataSetSheetWriterTest Unit Tests
    ///</summary>
    [TestClass()]
    public class XLDataSetSheetWriterTest
    {


        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        // 
        //You can use the following additional attributes as you write your tests:
        //
        //Use ClassInitialize to run code before running the first test in the class
        //[ClassInitialize()]
        //public static void MyClassInitialize(TestContext testContext)
        //{
        //}
        //
        //Use ClassCleanup to run code after all tests in a class have run
        //[ClassCleanup()]
        //public static void MyClassCleanup()
        //{
        //}
        //
        //Use TestInitialize to run code before running each test
        //[TestInitialize()]
        //public void MyTestInitialize()
        //{
        //}
        //
        //Use TestCleanup to run code after each test has run
        //[TestCleanup()]
        //public void MyTestCleanup()
        //{
        //}
        //
        #endregion


        /// <summary>
        ///A test for Write
        ///</summary>
        [TestMethod()]
        public void WriteTest()
        {
            using (SqlConnection cnn = new SqlConnection(@"Server=INSDESKTOP01\SQL2008; Database=TestXL; Integrated Security=SSPI"))
            {
                cnn.Open();
                using (SqlCommand cmd = cnn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = @"
SELECT 
    e.Id, e.Name, e.Description, e.Date, e.Value, e.IsCyclic, et.Name AS ExpenseType, YEAR(e.Date) AS ExpenseYear
FROM dbo.Expenses e
INNER JOIN dbo.ExpenseTypes et on e.ExpenseType = et.Code
";
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        string tableName = "Expenses";
                        DataSet data = new DataSet();
                        da.Fill(data, tableName);
                        string sheetCodeName = "Sheet1";
                        string workbookPath = @"C:\Data\Office\Pivot.xlsx";
                        string fileName = @"C:\Data\Office\" + System.IO.Path.GetRandomFileName() + ".xlsx";
                        System.IO.File.Copy(workbookPath, fileName);
                        IXLSheetWriter<DataSet> target = new XLDataSetSheetWriter(data, sheetCodeName, fileName, tableName);
                        target.Write();
                        Assert.Inconclusive("A method that does not return a value cannot be verified.");
                    }
                }
                cnn.Close();
            }
        }
    }
}
